import datetime
import pymysql

# 数据库操作
config = {
    "host": "localhost",
    "user": "root",
    "password": "xuhuan.59",
    "database": "my_deicide",
    "charset": "utf8"
}

conn = pymysql.connect(**config)


# 根据日期来查询当日记录
def find_notepad_all(type):
    cursor = conn.cursor()
    query = ("select * from t_notepad where type='%s'" % type) if (type!=None and len(type)>0) else ("select * from t_notepad")

    print("---------------------------------")
    print(query)
    print("---------------------------------")
    cursor.execute(query)
    result = cursor.fetchall()
    list_data = []

    for (item) in result:
        item_map = {}
        item_map['id'] = item[0]
        item_map['title'] = item[1]
        item_map['content'] = item[2]
        list_data.append(item_map)
    cursor.close()
    return list_data


# 根据日期来查询当日记录
def find_notepad_id(id):
    cursor = conn.cursor()
    query = "select * from t_notepad where id=%s" % id
    cursor.execute(query)
    result = cursor.fetchall()

    item_map = {}
    for (item) in result:
        item_map['id'] = item[0]
        item_map['title'] = item[1]
        item_map['content'] = item[2]
        item_map['type'] = item[5]
        # print(item)
    cursor.close()
    return item_map


# 修改一条记录
def change_notepad_info(title, content, id, type):
    if id == None or id == 0 or len(id) == 0:
        add_notepad_task(title, content, type)
        return
    cursor = conn.cursor()
    sql = "UPDATE t_notepad SET title = '%s',content='%s',updata_time='%s',type='%s' WHERE id = '%s'" % (title, content, datetime.datetime.now().strftime('%Y-%m-%d'), type, id)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


# 删除一条记录
def del_notepad_info(id):
    cursor = conn.cursor()
    sql = "DELETE FROM t_notepad WHERE id = '%s'" % (id)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


# 添加一条记录
def add_notepad_task(title, content, type):
    cursor = conn.cursor()
    my_time = datetime.datetime.now().strftime('%Y-%m-%d')
    sql = "INSERT INTO t_notepad (title, content, create_time, updata_time, type) VALUES ('%s','%s','%s', '%s', '%s')" % (title,
                   content, my_time, my_time, type)
    print(sql)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


if __name__ == '__main__':
    print(find_notepad_all())
    # add_task('你好测试数据', '优化小鹿直播数据展示页面')
    # change_info('优化小鹿直播数据展示页面', 1, 5)
    conn.close()